
//Namespace
package com.katujo.squirrel;

//Java imports
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;
import java.util.concurrent.ConcurrentHashMap;

import javax.sql.DataSource;

/**
 * The squirrel manager inserts, deletes, updates and selects objects
 * from the database. 
 * 
 * <br/><br/>
 * Copyright 2010 Katujo LTD<br/>
 * Licensed under the Apache License, Version 2.0<br/>
 * (the "License"); you may not use this file except in compliance with the License. You may<br/>
 * obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required <br/>
 * by applicable law or agreed to in writing, software distributed under the License is distributed <br/>
 * on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either <br/>
 * express or implied. See the License for the specific language governing permissions and <br/>
 * limitations under the License. <br/>
 * 
 * @author Johan Hertz
 *
 */
public class SquirrelManager
{
	//Fields
	private final DataSource source;
	private final ConcurrentHashMap<Class<?>, SquirrelObject> objects;
	private final int batchSize = 1000;
	
	/**
	 * Create the Object 
	 *
	 */
	public SquirrelManager()
	{		
		//Set data source to null
		source = null;
		
		//Create the maps
		objects = new ConcurrentHashMap<Class<?>, SquirrelObject>();
	}	
		
	/**
	 * Create the Object with the data source to use 
	 * @param source
	 */
	public SquirrelManager(DataSource source)
	{
		//Set the source
		this.source = source;
		
		//Create the maps
		objects = new ConcurrentHashMap<Class<?>, SquirrelObject>();
	}
	
	/**
	 * Insert the item in the data base.
	 * <p>
	 * If the table has auto increment column the function will try to get
	 * the generated key for that column and set it back to the field.
	 * </p>
	 * @param <T>
	 * @param item
	 * @throws SquirrelException
	 */
	public <T> void insert(T item) throws SquirrelException
	{
		//Fields
		Connection connection = null;
		
		//Try to insert the item
		try
		{
			//Get a connection
			connection = getConnection();
			
			//Set auto commit
			connection.setAutoCommit(false);
			
			//Insert the item
			insert(connection, item);
			
			//Commit
			connection.commit();			
		}
		
		//Failed
		catch(Exception ex)
		{
			//Rollback
			try{connection.rollback();} catch(Throwable t){}
			
			//Just re-throw if SquirrelException
			if(ex instanceof SquirrelException)
				throw (SquirrelException) ex;
			
			//Throw wrapped exception
			else
				throw new SquirrelException("Failed to insert object: " + item.getClass().getSimpleName(), ex);
		}
		
		//Clean up
		finally
		{
			try{connection.close();}
			catch(Throwable t){}
		}
	}
	
	/**
	 * Insert the item in the data base.
	 * <p>
	 * If the table has auto increment column the function will try to get
	 * the generated key for that column and set it back to the field.
	 * </p>
	 * @param <T>
	 * @param item
	 * @throws Exception
	 */	
	public <T> void insert(Connection connection, T item) throws SquirrelException
	{
		//Fields
		PreparedStatement statement = null;
		ResultSet result = null;
				
		//Try to insert the object
		try
		{						
			//Get the SquirrelObject
			SquirrelObject squirrel = this.getSquirrelObject(item);
			
			//Get the SQL
			String sql = squirrel.getInsert();
						
			//Create the statement 
			statement = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
			
			//Set the parameters
			setParamters(statement, squirrel.getFields(), squirrel.getAutoIncrementField(), item);
			
			//Run the statement
			statement.execute();			
			
			//Set the auto ID if auto increment is set
			if(squirrel.getAutoIncrementField() != null)
			{
				//Get the result
				result = statement.getGeneratedKeys();		
				
				//Move to the value
				result.next();
				
				//Read the result into object auto field
				writeValue(result, 1, squirrel.getAutoIncrementField(), item);
			}			
		}
		
		//Failed
		catch(Exception ex)
		{
			//Object set 
			if(item != null)
				throw new SquirrelException("Failed to insert item: " + item.getClass().getSimpleName(), ex);
			
			//Object not set
			else throw new SquirrelException("Failed to insert item, item can't be null ", ex);
		}	
		
		//Clean up
		finally
		{
			try{if(result != null) result.close();} catch(Throwable t){}
			try{statement.close();} catch(Throwable t){}
		}
	}
	
	/**
	 * Insert the items in the data base.
	 * <p>
	 * This does not add any auto IDs to the items. If you need ID updates 
	 * use the single item insert in a loop. 
	 * </p>
	 * @param <T>
	 * @param items
	 * @throws SquirrelException
	 */	
	public <T> void insert(Collection<T> items) throws SquirrelException
	{
		//Fields
		Connection connection = null;
		
		//Try to insert the item
		try
		{
			//Get a connection
			connection = getConnection();
			
			//Set auto commit
			connection.setAutoCommit(false);
			
			//Insert the items
			insert(connection, items);
			
			//Commit
			connection.commit();			
		}
		
		//Failed
		catch(Exception ex)
		{
			//Rollback
			try{connection.rollback();} catch(Throwable t){}
			
			//Just re-throw if SquirrelException
			if(ex instanceof SquirrelException)
				throw (SquirrelException) ex;
			
			//Throw wrapped exception
			else
				throw new SquirrelException("Failed to insert items", ex);
		}
		
		//Clean up
		finally
		{
			try{connection.close();} catch(Throwable t){}
		}
	}	
	
	/**
	 * Insert the items in the data base.
	 * <p>
	 * This does not add any auto IDs to the items. If you need ID updates 
	 * use the single item insert in a loop. 
	 * </p>
	 * @param <T>
	 * @param connection
	 * @param items
	 * @throws SquirrelException
	 */
	public <T> void insert(Connection connection, Collection<T> items) throws SquirrelException
	{
		//Fields
		PreparedStatement statement = null;
		ResultSet result = null;		
		
		//Try to insert objects
		try
		{
			//Don't try to insert if no items
			if(items.size() == 0)
				return;
			
			//Create the squirrel object
			SquirrelObject squirrel = null;
			
			//Count the inserts
			int count = 0;
			
			//Insert the items
			for(T item : items)
			{
				//Do initial setup
				if(squirrel == null)
				{												
					//Get the SquirrelObject
					squirrel = getSquirrelObject(item);						
					
					//Get the SQL
					String sql = squirrel.getInsert();	
					
					//Create the statement
					statement = connection.prepareStatement(sql);					
				}
				
				//Set the parameters
				setParamters(statement, squirrel.getFields(), squirrel.getAutoIncrementField(), item);
				
				//Run the statement
				statement.addBatch();
				
				//Check if execute the batch
				if(count % batchSize == 0)
					statement.executeBatch();										
			}
			
			//Run the last batch
			statement.executeBatch();			
		}
		
		//Failed
		catch(Exception ex)
		{
			throw new SquirrelException("Failed to insert items", ex);
		}
		
		//Clean up
		finally
		{
			try{if(result != null) result.close();} catch(Throwable t){}
			try{statement.close();} catch(Throwable t){}
		}		
	}
	
	/**
	 * Update the item in the database.
	 * @param <T>
	 * @param connection
	 * @param item
	 * @throws SquirrelException
	 */
	public <T> void update(T item) throws SquirrelException
	{
		//Fields
		Connection connection = null;
		
		//Try to update the item
		try
		{
			//Get a connection
			connection = getConnection();
			
			//Set auto commit
			connection.setAutoCommit(false);
			
			//Update the item
			update(connection, item);
			
			//Commit
			connection.commit();			
		}
		
		//Failed
		catch(Exception ex)
		{
			//Rollback
			try{connection.rollback();} catch(Throwable t){}
			
			//Just re-throw if SquirrelException
			if(ex instanceof SquirrelException)
				throw (SquirrelException) ex;
			
			//Throw wrapped exception
			else
				throw new SquirrelException("Failed to update object: " + item.getClass().getSimpleName(), ex);
		}
		
		//Clean up
		finally
		{
			try{connection.close();} catch(Throwable t){}
		}
	}	
	
	/**
	 * Update the item in the database.
	 * @param <T>
	 * @param connection
	 * @param item
	 * @throws SquirrelException
	 */
	public <T> void update(Connection connection, T item) throws SquirrelException
	{
		//Fields
		PreparedStatement statement = null;
		
		//Try to update the item
		try
		{						
			//Get the SquirrelObject
			SquirrelObject squirrel = getSquirrelObject(item);
			
			//Get the SQL
			String sql = squirrel.getUpdate();
						
			//Create the statement
			statement = connection.prepareStatement(sql);
			
			//Set the parameters
			setParamters(statement, squirrel.getNoneIDFields(), item);
			setParamters(statement, squirrel.getNoneIDFields().size() + 1, squirrel.getIdFields(), item);
			
			//Run the statement
			statement.execute();
		}
		
		//Failed 
		catch(Exception ex)
		{
			//Null
			if(item == null)
				throw new SquirrelException("Failed to update item, item cannot be null", ex);
			
			//Not null
			else throw new SquirrelException("Failed to update item: " + item.getClass().getSimpleName(), ex);
		}
		
		//Clean up
		finally
		{
			try{statement.close();} catch(Throwable t) {}
		}
	}
	
	/**
	 * Update the items in the collection.
	 * @param <T>
	 * @param items
	 * @throws SquirrelException
	 */
	public <T> void update(Collection<T> items) throws SquirrelException
	{
		//Fields
		Connection connection = null;
		
		//Try to update the item
		try
		{
			//Get a connection
			connection = getConnection();
			
			//Set auto commit
			connection.setAutoCommit(false);
			
			//Update the items
			update(connection, items);
			
			//Commit
			connection.commit();			
		}
		
		//Failed
		catch(Exception ex)
		{
			//Rollback
			try{connection.rollback();} catch(Throwable t){}
			
			//Just re-throw if SquirrelException
			if(ex instanceof SquirrelException)
				throw (SquirrelException) ex;
			
			//Throw wrapped exception
			else throw new SquirrelException("Failed to update items", ex);
		}
		
		//Clean up
		finally
		{
			try{connection.close();} catch(Throwable t){}
		}
	}		
	
	/**
	 * Update the items in the collection.
	 * @param <T>
	 * @param connection
	 * @param item
	 * @throws SquirrelException
	 */
	public <T> void update(Connection connection, Collection<T> items) throws SquirrelException
	{
		//Fields
		PreparedStatement statement = null;
		
		//Try to update the item
		try
		{
			//Don't try to update if no items
			if(items.size() == 0)
				return;
			
			//Create the squirrel object
			SquirrelObject squirrel = null;
			
			//Count the inserts
			int count = 0;
			
			//Update the items
			for(T item : items)
			{
				//Do initial setup
				if(squirrel == null)
				{										
					//Get the SquirrelObject
					squirrel = getSquirrelObject(item);						
					
					//Get the SQL
					String sql = squirrel.getUpdate();	
					
					//Create the statement
					statement = connection.prepareStatement(sql);					
				}
				
				//Set the parameters
				setParamters(statement, squirrel.getNoneIDFields(), item);
				setParamters(statement, squirrel.getNoneIDFields().size() + 1, squirrel.getIdFields(), item);
				
				//Run the statement
				statement.addBatch();
				
				//Check if execute the batch
				if(count % batchSize == 0)
					statement.executeBatch();										
			}
			
			//Run the last batch
			statement.executeBatch();
		}
		
		//Failed 
		catch(Exception ex)
		{			
			throw new SquirrelException("Failed to update items", ex);
		}
		
		//Clean up
		finally
		{
			try{statement.close();} catch(Throwable t){}
		}
	}	
	
	/**
	 * Delete the item in the database.
	 * @param <T>
	 * @param connection
	 * @param item
	 * @throws SquirrelException
	 */
	public <T> void delete(T item) throws SquirrelException
	{
		//Fields
		Connection connection = null;
		
		//Try to delete the item
		try
		{
			//Get a connection
			connection = getConnection();
			
			//Set auto commit
			connection.setAutoCommit(false);
			
			//Delete the item
			delete(connection, item);
			
			//Commit
			connection.commit();			
		}
		
		//Failed
		catch(Exception ex)
		{
			//Rollback
			try{connection.rollback();} catch(Throwable t){}
			
			//Just re-throw if SquirrelException
			if(ex instanceof SquirrelException)
				throw (SquirrelException) ex;
			
			//Throw wrapped exception
			else throw new SquirrelException("Failed to delete object: " + item.getClass().getSimpleName(), ex);
		}
		
		//Clean up
		finally
		{
			try{connection.close();} catch(Throwable t){}
		}
	}		
		
	/**
	 * Delete the item. 
	 * @param <T>
	 * @param connection
	 * @param item
	 * @throws SquirrelException
	 */
	public <T> void delete(Connection connection, T item) throws SquirrelException
	{
		//Fields
		PreparedStatement statement = null;
		
		//Try to delete the item
		try
		{						
			//Get the SquirrelObject
			SquirrelObject squirrel = getSquirrelObject(item);
			
			//Get the SQL
			String sql = squirrel.getDelete();

			//Create the statement
			statement = connection.prepareStatement(sql);
			
			//Set the parameters
			setParamters(statement, squirrel.getIdFields(), item);
			
			//Run the statement
			statement.execute();
		}
		
		//Failed 
		catch(Exception ex)
		{
			//Null
			if(item == null)
				throw new SquirrelException("Failed to delete item, item cannot be null", ex);
			
			//Not null
			else throw new SquirrelException("Failed to delete item: " + item.getClass().getSimpleName(), ex);
		}
		
		//Clean up
		finally
		{
			try{statement.close();} catch(Throwable t){}
		}
	}		
	
	/**
	 * Delete the items in the collection.
	 * @param <T>
	 * @param items
	 * @throws SquirrelException
	 */
	public <T> void delete(Collection<T> items) throws SquirrelException
	{
		//Fields
		Connection connection = null;
		
		//Try to delete the item
		try
		{
			//Get a connection
			connection = getConnection();
			
			//Set auto commit
			connection.setAutoCommit(false);
			
			//Delete the items
			delete(connection, items);
			
			//Commit
			connection.commit();			
		}
		
		//Failed
		catch(Exception ex)
		{
			//Rollback
			try{connection.rollback();} catch(Throwable t){}
			
			//Just re-throw if SquirrelException
			if(ex instanceof SquirrelException)
				throw (SquirrelException) ex;
			
			//Throw wrapped exception
			else throw new SquirrelException("Failed to delete items", ex);
		}
		
		//Clean up
		finally
		{
			try{connection.close();} catch(Throwable t){}
		}
	}		
	
	/**
	 * Delete the items in the collection.
	 * @param <T>
	 * @param connection
	 * @param items
	 * @throws SquirrelException
	 */
	public <T> void delete(Connection connection, Collection<T> items) throws SquirrelException
	{
		//Fields
		PreparedStatement statement = null;
		
		//Try to delete the item
		try
		{
			//Don't try to delete if no items
			if(items.size() == 0)
				return;
			
			//Create the squirrel object
			SquirrelObject squirrel = null;
			
			//Count the inserts
			int count = 0;
			
			//Delete the items
			for(T item : items)
			{
				//Do initial setup
				if(squirrel == null)
				{										
					//Get the SquirrelObject
					squirrel = getSquirrelObject(item);						
					
					//Get the SQL
					String sql = squirrel.getDelete();	
					
					//Create the statement
					statement = connection.prepareStatement(sql);					
				}
				
				//Set the parameters
				setParamters(statement, squirrel.getIdFields(), item);
				
				//Run the statement
				statement.addBatch();
				
				//Check if execute the batch
				if(count % batchSize == 0)
					statement.executeBatch();										
			}
			
			//Run the last batch
			statement.executeBatch();
		}
		
		//Failed 
		catch(Exception ex)
		{			
			throw new SquirrelException("Failed to delete items", ex);
		}
		
		//Clean up
		finally
		{
			try{statement.close();} catch(Throwable t){}
		}
	}		
	
	/**
	 * Try to load all instances of the class form the database.
	 * @param <T>
	 * @param clazz
	 * @return
	 * @throws SquirrelException
	 */
	public <T> List<T> selectAll(Class<?> clazz) throws SquirrelException
	{
		//Fields
		Connection connection = null;
		
		//Try to delete the item
		try
		{
			//Get a connection
			connection = getConnection();
						
			//Select the items
			return selectAll(connection, clazz, null);			
		}
		
		//Failed
		catch(Exception ex)
		{			
			//Just re-throw if SquirrelException
			if(ex instanceof SquirrelException)
				throw (SquirrelException) ex;
			
			//Throw wrapped exception
			else throw new SquirrelException("Failed to select all items: " + clazz.getSimpleName() , ex);
		}
		
		//Clean up
		finally
		{
			try{connection.close();} catch(Throwable t){}
		}
	}		
	
	/**
	 * Try to load all instances of the class form the database.
	 * <p>
	 * Order sets the ORDER BY of the SQL. Format will be SQL + 
	 * "ORDER BY " + order
	 * </p>
	 * @param <T>
	 * @param clazz
	 * @param order
	 * @return
	 * @throws SquirrelException
	 */
	public <T> List<T> selectAll(Class<?> clazz, String order) throws SquirrelException
	{
		//Fields
		Connection connection = null;
		
		//Try to delete the item
		try
		{
			//Get a connection
			connection = getConnection();
						
			//Select the items
			return selectAll(connection, clazz, order);			
		}
		
		//Failed
		catch(Exception ex)
		{			
			//Just re-throw if SquirrelException
			if(ex instanceof SquirrelException)
				throw (SquirrelException) ex;
			
			//Throw wrapped exception
			else throw new SquirrelException("Failed to select all items: " + clazz.getSimpleName() , ex);
		}
		
		//Clean up
		finally
		{
			try{connection.close();} catch(Throwable t){}
		}
	}		

	/**
	 * Try to load all instances of the class form the database.
	 * @param <T>
	 * @param connection
	 * @param clazz
	 * @return
	 * @throws SquirrelException
	 */
	public <T> List<T> selectAll(Connection connection, Class<?> clazz) throws SquirrelException
	{
		return selectAll(connection, clazz, null);
	}
	
	/**
	 * Try to load all instances of the class form the database.
	 * <p>
	 * Order sets the ORDER BY of the SQL. Format will be SQL + 
	 * "ORDER BY " + order
	 * </p>
	 * @param connection
	 * @param clazz
	 * @param order
	 * @return
	 * @throws SquirrelException
	 */
	public <T> List<T> selectAll(Connection connection, Class<?> clazz, String order) throws SquirrelException
	{
		//Fields
		PreparedStatement statement = null;
		ResultSet result = null;
		
		//Try to select all items
		try
		{						
			//Get the SquirrelObject
			SquirrelObject squirrel = getSquirrelObject(clazz);
			
			//Get the SQL
			String sql = squirrel.getSelectAll();
			
			//Add order if set
			if(order != null)
				sql = sql + "\nORDER BY \n" + order;		
			
			//Create the statement
			statement = connection.prepareStatement(sql);
						
			//Run the statement
			result = statement.executeQuery();
							
			//Create the list to hold the data
			List<T> list = new ArrayList<T>();
			
			//Get the fields
			Set<Field> fields = squirrel.getFields();
			
			//Read the data into the list
			while(result.next())
			{
				//Create the item
				@SuppressWarnings("unchecked")
				T item = (T) squirrel.createObject();
				
				//Create the index
				int index = 1;
				
				//Read the data into the fields
				for(Field field : fields)
					writeValue(result, index++, field, item);
				
				//Add the item to the list
				list.add(item);
			}
			
			//Return the list
			return list;			
		}
		
		//Failed
		catch(Exception ex)
		{		
			throw new SquirrelException("Failed to select all: " + clazz.getSimpleName(), ex);
		}
		
		//Clean up
		finally
		{
			try{result.close();} catch(Throwable t) {}
			try{statement.close();} catch(Throwable t) {}
		}		
	}
		
	/**
	 * Select a item of the class using the keys in the object array.
	 * <p>
	 * The class must have the same number of ID fields as the length
	 * of the provided keys.
	 * </p>
	 * @param <T>
	 * @param clazz
	 * @param keys
	 * @return
	 * @throws SquirrelException
	 */
	@SuppressWarnings("unchecked")
	public <T> T select(Class<?> clazz, Object ... keys) throws SquirrelException
	{
		//Fields
		Connection connection = null;
		
		//Try to select items
		try
		{
			//Get a connection
			connection = getConnection();
						
			//Select the items
			return (T) select(connection, clazz, keys);			
		}
		
		//Failed
		catch(Exception ex)
		{			
			//Just re-throw if SquirrelException
			if(ex instanceof SquirrelException)
				throw (SquirrelException) ex;
			
			//Throw wrapped exception
			else throw new SquirrelException("Failed to select item using keys for class: " + clazz.getSimpleName() , ex);
		}
		
		//Clean up
		finally
		{
			try{connection.close();} catch(Throwable t){}
		}				
	}
	
	/**
	 * Select a item of the class using the keys in the object array.
	 * <p>
	 * The class must have the same number of ID fields as the length
	 * of the provided keys.
	 * </p>
	 * @param <T>
	 * @param connection
	 * @param clazz
	 * @param keys
	 * @return
	 * @throws SquirrelException
	 */
	public <T> T select(Connection connection, Class<?> clazz, Object ... keys) throws SquirrelException
	{
		//Try to select item
		try
		{			
			//Get the squirrel 
			SquirrelObject squirrel = getSquirrelObject(clazz);
						
			//Check that the keys and the IDs match in length
			if(keys.length != squirrel.getIdFields().size())
				throw new SquirrelException("Keys and IDs does not match in length");
			
			//Create the parameters
			Map<String, Object> parameters = new HashMap<String, Object>();
			
			//Create the index
			int index = 0;
			
			//Add the ID fields to the parameters)
			for(Field field : squirrel.getIdFields())
			{
				//Get the column for the field
				String column = squirrel.getColumns().get(field);
				
				//Add to the parameters
				parameters.put(column, keys[index++]);				
			}
					
			//Run the select
			List<T> list = this.select(connection, clazz, parameters);
			
			//No match found
			if(list.size() == 0)
				return null;
			
			//Return the first match
			return list.get(0);
		}
		
		//Failed
		catch (Exception ex)
		{
			throw new SquirrelException("Failed to select: " + clazz.getSimpleName(), ex);
		}
	}
		
	/**
	 * Select a item that is equals to the like item. More 
	 * specifically where the fields marked with @ID for the
	 * items are the same.  
	 * @param <T>
	 * @param like
	 * @return
	 * @throws SquirrelException
	 */
	public <T> T select(T like) throws SquirrelException
	{
		//Fields
		Connection connection = null;
		
		//Try to select the item
		try
		{
			//Get a connection
			connection = getConnection();
						
			//Select the item
			return select(connection, like);			
		}
		
		//Failed
		catch(Exception ex)
		{			
			//Just re-throw if SquirrelException
			if(ex instanceof SquirrelException)
				throw (SquirrelException) ex;
			
			//Throw wrapped exception
			else throw new SquirrelException("Failed to select items of type " + like.getClass().getSimpleName(), ex);
		}
		
		//Clean up
		finally
		{
			try{connection.close();} catch(Throwable t){}
		}
	}	
	
	/**
	 * Select a item that is equals to the like item. More 
	 * specifically where the fields marked with @ID for the
	 * items are the same.  
	 * @param <T>
	 * @param connection
	 * @param like
	 * @return
	 * @throws SquirrelException
	 */
	public <T> T select(Connection connection, T like) throws SquirrelException
	{
		//Fields
		PreparedStatement statement = null;
		ResultSet result = null;
		
		//Try to select all items
		try
		{						
			//Get the SquirrelObject
			SquirrelObject squirrel = getSquirrelObject(like);
			
			//Get the SQL
			String sql = squirrel.getSelect();
						
			//Create the statement
			statement = connection.prepareStatement(sql);
			
			//Get the ID fields
			Set<Field> ids = squirrel.getIdFields();
			
			//Set the parameters
			SquirrelManager.setParamters(statement, ids, like);
						
			//Run the statement
			result = statement.executeQuery();
						
			//Read the data into the list
			if(result.next())
			{				
				//Create the item
				@SuppressWarnings("unchecked")
				T item = (T) squirrel.createObject();
				
				//Create the index
				int index = 1;
				
				//Get the fields
				Set<Field> fields = squirrel.getFields();								
				
				//Read the data into the fields
				for(Field field : fields)
					writeValue(result, index++, field, item);
				
				//Return the item
				return item;
			}
			
			//No match found			
			return null;
		}
		
		//Failed
		catch(Exception ex)
		{		
			//Set
			if(like != null)			
				throw new SquirrelException("Failed to select item of type " + like.getClass().getSimpleName(), ex);
			
			//Null
			else throw new SquirrelException("Failed to select item, like can not be null", ex);
		}
		
		//Clean up
		finally
		{
			try{result.close();} catch(Throwable t) {}
			try{statement.close();} catch(Throwable t) {}
		}		
	}
		
	/**
	 * Select instance of the class using a parameters map to 
	 * set the WHERE.
	 * <p>
	 * The parameters map will be set in order of the iterator,
	 * if order is important used LinkedHashMap.
	 * </p>
	 * @param <T>
	 * @param clazz
	 * @param parameters
	 * @return
	 * @throws SquirrelException
	 */
	public <T> List<T> select(Class<?> clazz, Map<String, Object> parameters) throws SquirrelException
	{
		//Fields
		Connection connection = null;
		
		//Try to select the item
		try
		{
			//Get a connection
			connection = getConnection();
						
			//Select the items
			return select(connection, clazz, parameters, null);			
		}
		
		//Failed
		catch(Exception ex)
		{			
			//Just re-throw if SquirrelException
			if(ex instanceof SquirrelException)
				throw (SquirrelException) ex;
			
			//Throw wrapped exception
			else throw new SquirrelException("Failed to select items using parameters map for type " + clazz.getSimpleName(), ex);
		}
		
		//Clean up
		finally
		{
			try{connection.close();} catch(Throwable t){}
		}
	}		
	
	/**
	 * Select instance of the class using a parameters map to 
	 * set the WHERE.
	 * <p>
	 * The parameters map will be set in order of the iterator,
	 * if order is important used LinkedHashMap.
	 * </p>
	 * <p>
	 * Order sets the ORDER BY of the SQL. Format will be SQL + 
	 * "ORDER BY " + order
	 * </p> 
	 * @param <T>
	 * @param clazz
	 * @param parameters
	 * @param order
	 * @return
	 * @throws SquirrelException
	 */
	public <T> List<T> select(Class<?> clazz, Map<String, Object> parameters, String order) throws SquirrelException
	{
		//Fields
		Connection connection = null;
		
		//Try to select the item
		try
		{
			//Get a connection
			connection = getConnection();
						
			//Select the items
			return select(connection, clazz, parameters, order);			
		}
		
		//Failed
		catch(Exception ex)
		{			
			//Just re-throw if SquirrelException
			if(ex instanceof SquirrelException)
				throw (SquirrelException) ex;
			
			//Throw wrapped exception
			else throw new SquirrelException("Failed to select items using parameters map for type " + clazz.getSimpleName(), ex);
		}
		
		//Clean up
		finally
		{
			try{connection.close();} catch(Throwable t){}
		}
	}	
	
	/**
	 * Select instance of the class using a parameters map to 
	 * set the WHERE.
	 * <p>
	 * The parameters map will be set in order of the iterator,
	 * if order is important used LinkedHashMap.
	 * </p>
	 * @param <T>
	 * @param connection
	 * @param clazz
	 * @param parameters
	 * @return
	 * @throws SquirrelException
	 */
	public <T> List<T> select(Connection connection, Class<?> clazz, Map<String, Object> parameters) throws SquirrelException
	{
		return select(connection, clazz, parameters, null);
	}
	
	/**
	 * Select instance of the class using a parameters map to 
	 * set the WHERE.
	 * <p>
	 * The parameters map will be set in order of the iterator,
	 * if order is important used LinkedHashMap.
	 * </p>
	 * <p>
	 * Order sets the ORDER BY of the SQL. Format will be SQL + 
	 * "ORDER BY " + order
	 * </p>
	 * @param <T>
	 * @param connection
	 * @param clazz
	 * @param parameters
	 * @param order
	 * @return
	 * @throws SquirrelException
	 */	
	public <T> List<T> select(Connection connection, Class<?> clazz, Map<String, Object> parameters, String order) throws SquirrelException
	{
		//Fields
		PreparedStatement statement = null;
		ResultSet result = null;
		
		//Try to select items
		try
		{						
			//Get the SquirrelObject
			SquirrelObject squirrel = getSquirrelObject(clazz);
			
			//Get the SQL
			String sql = squirrel.getSelectAll();
						
			//Add the parameter holders
			if(parameters.size() > 0)
			{
				//Create the builder
				StringBuilder builder = new StringBuilder();
				
				//Add WHERE
				builder.append("\nWHERE \n");
				
				//Create the index
				int index = 0;
				
				//Add the parameter to the builder
				for(String column : parameters.keySet())
				{					
					//Last
					if(index == parameters.size()-1)
					{	
						builder.append("\t`");
						builder.append(column);
						builder.append("` = ?");
					}
					
					//In the middle
					else
					{
						builder.append("\t`");
						builder.append(column);
						builder.append("` = ? AND ");						
					}
					
					//Add to the index
					index++;					
				}	
				
				//Add the builder string to the SQL
				sql += builder.toString();
			}
			
			//Add order if set
			if(order != null)
				sql = sql + "\nORDER BY \n" + order;	
							
			//Create the statement
			statement = connection.prepareStatement(sql);
			
			//Set the parameters
			setParamtersMap(statement, parameters);
						
			//Run the statement
			result = statement.executeQuery();
							
			//Create the list to hold the data
			List<T> list = new ArrayList<T>();
			
			//Get the fields
			Set<Field> fields = squirrel.getFields();
			
			//Read the data into the list
			while(result.next())
			{
				//Create the item		
				@SuppressWarnings("unchecked")
				T item = (T) squirrel.createObject();
				
				//Create the index
				int index = 1;
				
				//Read the data into the fields
				for(Field field : fields)
					writeValue(result, index++, field, item);
				
				//Add the item to the list
				list.add(item);
			}
			
			//Return the list
			return list;			
		}
		
		//Failed
		catch(Exception ex)
		{		
			throw new SquirrelException("Failed to select items using parameters map for type " + clazz.getSimpleName(), ex);
		}
		
		//Clean up
		finally
		{
			try{result.close();} catch(Throwable t) {}
			try{statement.close();} catch(Throwable t) {}
		}		
	}
	
	/**
	 * Select instance of the class using a PreparedStatement.
	 * @param <T>
	 * @param clazz
	 * @param statement
	 * @return
	 * @throws SquirrelException
	 */
	public <T> List<T> select(Class<?> clazz, PreparedStatement statement) throws SquirrelException
	{
		//Fields
		Connection connection = null;
		
		//Try to select the item
		try
		{
			//Get a connection
			connection = getConnection();
						
			//Select the items
			return select(connection, clazz, statement);			
		}
		
		//Failed
		catch(Exception ex)
		{			
			//Just re-throw if SquirrelException
			if(ex instanceof SquirrelException)
				throw (SquirrelException) ex;
			
			//Throw wrapped exception
			else throw new SquirrelException("Failed to select items using statement for " + clazz.getSimpleName(), ex);
		}
		
		//Clean up
		finally
		{
			try{connection.close();} catch(Throwable t){}
		}
	}	
	
	/**
	 * Select instance of the class using a PreparedStatement.
	 * @param <T>
	 * @param connection
	 * @param clazz
	 * @param statement
	 * @return
	 * @throws SquirrelException
	 */
	public <T> List<T> select(Connection connection, Class<?> clazz, PreparedStatement statement) throws SquirrelException
	{
		//Fields
		ResultSet result = null;
		
		//Try to select all items
		try
		{						
			//Get the SquirrelObject
			SquirrelObject squirrel = getSquirrelObject(clazz);
										
			//Run the statement
			result = statement.executeQuery();
							
			//Create the list to hold the data
			List<T> list = new ArrayList<T>();
			
			//Get the fields
			Set<Field> fields = squirrel.getFields();
						
			//Read the data into the list
			while(result.next())
			{
				//Create the item
				@SuppressWarnings("unchecked")
				T item = (T) squirrel.createObject();
								
				//Read the data into the fields
				for(Field field : fields)
					writeValue(result, squirrel.getColumns().get(field), field, item);
				
				//Add the item to the list
				list.add(item);
			}
			
			//Return the list
			return list;			
		}
		
		//Failed
		catch(Exception ex)
		{		
			throw new SquirrelException("Failed to select items using statement for " + clazz.getSimpleName(), ex);
		}
		
		//Clean up
		finally
		{
			try{result.close();} catch(Throwable t) {}
		}		
	}	
		
	/**
	 * Select instance of the class using the SQL string.
	 * @param <T>
	 * @param clazz
	 * @param sql
	 * @param order
	 * @return
	 * @throws SquirrelException
	 */
	public <T> List<T> select(Class<?> clazz, String sql) throws SquirrelException
	{
		//Fields
		Connection connection = null;
		
		//Try to select the item
		try
		{
			//Get a connection
			connection = getConnection();
						
			//Select the items
			return select(connection, clazz, sql);			
		}
		
		//Failed
		catch(Exception ex)
		{			
			//Just re-throw if SquirrelException
			if(ex instanceof SquirrelException)
				throw (SquirrelException) ex;
			
			//Throw wrapped exception
			else throw new SquirrelException("Failed to select items SQL string " + clazz.getSimpleName(), ex);
		}
		
		//Clean up
		finally
		{
			try{connection.close();} catch(Throwable t){}
		}
	}	
			
	/**
	 * Select instance of the class using the SQL string.
	 * @param <T>
	 * @param connection
	 * @param clazz
	 * @param sql
	 * @param order
	 * @return
	 * @throws SquirrelException
	 */
	public <T> List<T> select(Connection connection, Class<?> clazz, String sql) throws SquirrelException
	{
		//Fields
		PreparedStatement statement = null;
		ResultSet result = null;
		
		//Try to select all items
		try
		{						
			//Get the SquirrelObject
			SquirrelObject squirrel = getSquirrelObject(clazz);
				
			//Create the statement
			statement = connection.prepareStatement(sql);
						
			//Run the statement
			result = statement.executeQuery();
							
			//Create the list to hold the data
			List<T> list = new ArrayList<T>();
			
			//Get the fields
			Set<Field> fields = squirrel.getFields();
						
			//Read the data into the list
			while(result.next())
			{
				//Create the item
				@SuppressWarnings("unchecked")
				T item = (T) squirrel.createObject();
								
				//Read the data into the fields
				for(Field field : fields)
					writeValue(result, squirrel.getColumns().get(field), field, item);
				
				//Add the item to the list
				list.add(item);
			}
			
			//Return the list
			return list;			
		}
		
		//Failed
		catch(Exception ex)
		{		
			throw new SquirrelException("Failed to select items SQL string " + clazz.getSimpleName(), ex);
		}
		
		//Clean up
		finally
		{
			try{result.close();} catch(Throwable t) {}
			try{statement.close();} catch(Throwable t) {}
		}		
	}	
	
	/**
	 * Select the object links for the item.
	 * @param <T>
	 * @param item
	 * @throws SquirrelException
	 */
	public <T> void selectLink(T item) throws SquirrelException
	{
		//Fields
		Connection connection = null;
		
		//Try to select the item
		try
		{
			//Get a connection
			connection = getConnection();
						
			//Select the link for the item
			selectLink(connection, item);			
		}
		
		//Failed
		catch(Exception ex)
		{			
			//Just re-throw if SquirrelException
			if(ex instanceof SquirrelException)
				throw (SquirrelException) ex;
			
			//Throw wrapped exception
			else throw new SquirrelException("Failed to select link for " + item.getClass().getSimpleName(), ex);
		}
		
		//Clean up
		finally
		{
			try{connection.close();} catch(Throwable t){}
		}
	}	
	
	/**
	 * Select the object links for the item.
	 * @param <T>
	 * @param connection
	 * @param item
	 * @throws SquirrelException
	 */
	public <T> void selectLink(Connection connection, T item) throws SquirrelException
	{
		selectLink(connection, item, null);
	}
	
	/**
	 * Select the object links for the item.
	 * @param <T>
	 * @param connection
	 * @param item
	 * @param depth
	 * @throws SquirrelException
	 */
	private <T> void selectLink(Connection connection, T item, Integer depth) throws SquirrelException
	{
		//Try to select the link for the item
		try
		{						
			//Get the SquirrelObject
			SquirrelObject squirrel = getSquirrelObject(item); 
							
			//Get link fields
			Set<Field> linkSet = squirrel.getLinkFields();
						
			//Get the link map
			Map<Field, Map<String, Object>> linkMap = squirrel.getLinkMap();
			
			//Load the link fields
			for(Field field : linkSet)
			{				
				//Set depth if not set
				if(depth == null)
					depth = squirrel.getLinkDepth().get(field);
				
				//Go to the next field if depth is less the one
				if(depth < 1)
					continue;
				
				//Create the parameters map
				Map<String, Object> parameters = new HashMap<String, Object>();
				
				//Get the columns
				Map<String, Object> columns = linkMap.get(field);
				
				//Populate the parameters map
				for(Entry<String, Object> entry : columns.entrySet())
				{	
					//Check if the value is a field
					if(entry.getValue() instanceof Field)
					{
						//Get the entry field
						Field entryField = (Field) entry.getValue();
						
						//Get the value
						Object value = entryField.get(item);
						
						//Add to the parameters
						parameters.put(entry.getKey(), value);
					}
					
					//Primitive value just add to the parameters
					else parameters.put(entry.getKey(), entry.getValue());
				}	
				
				//Load list
				if(field.getType() == List.class)
				{
					//Get the type
					ParameterizedType type = (ParameterizedType) field.getGenericType();
					
					//Get the class
					Class<?> clazz = (Class<?>) type.getActualTypeArguments()[0];
					
					//Run the select
					List<?> list = select(connection, clazz, parameters);					
					
					//Set the list to field
					field.set(item, list);		
					
					//Load the link for the items in the list
					for(Object childItem : list)						
						selectLink(connection, childItem, depth-1);
				}
				
				//Load map
				else if(field.getType() == Map.class)
				{					
					//Get the type
					ParameterizedType type = (ParameterizedType) field.getGenericType();
					
					//Get the class
					Class<?> clazz = (Class<?>) type.getActualTypeArguments()[1];
										
					//Get the child SquirrelObject
					SquirrelObject childSquirrel = getSquirrelObject(clazz);
													
					//Run the select
					List<?> list = select(connection, clazz, parameters);
					
					//Get the map
					@SuppressWarnings("unchecked")
					Map<Object, Object> map = (Map) field.get(item);
					
					//Get the key name
					String keyName = squirrel.getLinkKeys().get(field);
										
					//Load the link for the items in the list
					for(Object childItem : list)
					{																		
						//Get the key
						Field childField = childSquirrel.getFieldsMap().get(keyName);
						Object key = childField.get(childItem);
						
						//Add to the map
						map.put(key, childItem);
						
						//Load the link for the child item	
						selectLink(connection, childItem, depth-1);						
					}												
				}
				
				//Load single item
				else 
				{	
					//Load the list
					List<?> list = select(connection, field.getType(), parameters);
					
					//Check if anything where found
					if(list.size() > 0)					
					{	
						//Set the first item (don't care about if more where found)
						field.set(item, list.get(0));
						
						//Load child items
						selectLink(connection, list.get(0), depth-1);
					}
					
					//No match found
					else field.set(item, null);
				}					
			}
		}
		
		//Failed
		catch(Exception ex)
		{
			throw new SquirrelException("Failed to select link for " + item.getClass().getSimpleName(), ex);
		}
	}
	
	/**
	 * Insert the object links for a item.
	 * <p>
	 * <b>WARNING:</b> Normally this should always be with the same connection
	 * as the item was inserted with.
	 * </p>
	 * @param <T>
	 * @param item
	 * @throws SquirrelException
	 */
	public <T> void insertLinks(T item) throws SquirrelException
	{
		//Fields
		Connection connection = null;
		
		//Try to insert links
		try
		{
			//Get a connection
			connection = getConnection();
						
			//Insert the links for the item
			insertLinks(connection, item);			
		}
		
		//Failed
		catch(Exception ex)
		{			
			//Just re-throw if SquirrelException
			if(ex instanceof SquirrelException)
				throw (SquirrelException) ex;
			
			//Throw wrapped exception
			else throw new SquirrelException("Failed to insert links for " + item.getClass().getSimpleName(), ex);
		}
		
		//Clean up
		finally
		{
			try{connection.close();} catch(Throwable t){}
		}
	}

	/**
	 * Insert the object links for a item.
	 * @param <T>
	 * @param connection
	 * @param item
	 * @throws Exception
	 */
	public <T> void insertLinks(Connection connection, T item) throws SquirrelException
	{
		insertLinks(connection, item, null);
	}
	
	/**
	 * Insert the object links for a item.
	 * @param <T>
	 * @param connection
	 * @param item
	 * @param depth
	 * @throws SquirrelException
	 */
	private <T> void insertLinks(Connection connection, T item, Integer depth) throws SquirrelException
	{
		//Fields
		PreparedStatement statement = null;
		ResultSet result = null;		
		
		//Try to insert
		try
		{			
			//Get the SquirrelObject
			SquirrelObject squirrel = getSquirrelObject(item);
			
			//Get link fields that are not select only
			Set<Field> linkSet = squirrel.getLinkInsertDeleteFields();
						
			//Get the link map
			Map<Field, Map<String, Object>> linkMap = squirrel.getLinkMap();	
			
			//Load the link fields
			for(Field field : linkSet)
			{		
				//Get the depth if not set
				if(depth == null)
					depth = squirrel.getLinkDepth().get(field);
				
				//Go to the next field if depth less the one
				if(depth < 1)
					continue;
				
				//Get the child item
				Object childItem = field.get(item);
				
				//Don't try to insert if not set
				if(childItem != null)
				{					
					//Get the columns
					Map<String, Object> columns = linkMap.get(field);			
													
					//Insert list
					if(childItem instanceof List<?>)
					{
						//Get the list
						List<?> list = (List<?>) childItem;
						
						//Check it the list holds any items
						if(list.size() > 0)
						{
							//Get the type
							ParameterizedType type = (ParameterizedType) field.getGenericType();
							
							//Get the class
							Class<?> clazz  = (Class<?>) type.getActualTypeArguments()[0];
							
							//Get the child squirrel
							SquirrelObject childSquirrel = getSquirrelObject(clazz);	
							
							//Get the child columns 
							Map<Field, String> childColumns = childSquirrel.getColumns();							
							
							//Flip the child columns
							Map<String, Field> childColumnsFliped = new HashMap<String, Field>();
							for(Entry<Field, String> entry : childColumns.entrySet())
								childColumnsFliped.put(entry.getValue(), entry.getKey());
							
							//Get the SQL
							String sql = childSquirrel.getInsert();		
														
							//Create the statement 
							statement = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);							
							
							//Insert the items
							for(Object listItem : list)
							{
								//Set the fields to the item
								for(Entry<String, Object> entry : columns.entrySet())
								{	
									//Get the value
									Object value = null;
									
									//Check if the value is a field
									if(entry.getValue() instanceof Field)
									{
										//Get the entry field
										Field entryField = (Field) entry.getValue();
										
										//Get the value
										value = entryField.get(item);															
									}
									
									//Add override
									else value = entry.getValue();
									
									//Update object
									Field updateField = childColumnsFliped.get(entry.getKey());
									if(updateField != null)
										updateField.set(listItem, value);
								}
																											
								//Set the parameters
								setParamters(statement, childSquirrel.getFields(), childSquirrel.getAutoIncrementField(), listItem);
													
								//Run the statement
								statement.execute();	
								
								//Set the auto ID if auto increment is set
								if(squirrel.getAutoIncrementField() != null)
								{
									//Get the result
									result = statement.getGeneratedKeys();		
									
									//Move to the value
									result.next();
									
									//Read the result into object auto field
									writeValue(result, 1, childSquirrel.getAutoIncrementField(), listItem);	
									
									//Close result
									result.close();
								}	
								
								//Insert links for the item
								insertLinks(connection, listItem, depth-1);
							}
						}
					}
					
					//Insert list
					else if(childItem instanceof Map<?, ?>)
					{
						//Get the map
						Map<?, ?> map = (Map<?, ?>) childItem;

						//Check if the map holds any items
						if(map.size() > 0)
						{
							//Get the type
							ParameterizedType type = (ParameterizedType) field.getGenericType();
							
							//Get the class
							Class<?> clazz  = (Class<?>) type.getActualTypeArguments()[1];
														
							//Get the child squirrel
							SquirrelObject childSquirrel = getSquirrelObject(clazz);	
							
							//Get the child columns 
							Map<Field, String> childColumns = childSquirrel.getColumns();							
							
							//Flip the child columns
							Map<String, Field> childColumnsFliped = new HashMap<String, Field>();
							for(Entry<Field, String> entry : childColumns.entrySet())
								childColumnsFliped.put(entry.getValue(), entry.getKey());
							
							//Get the SQL
							String sql = childSquirrel.getInsert();							
							
							//Create the statement 
							statement = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);							
							
							//Insert the items
							for(Object mapItem : map.values())
							{
								//Set the fields to the item
								for(Entry<String, Object> entry : columns.entrySet())
								{	
									//Get the value
									Object value = null;
									
									//Check if the value is a field
									if(entry.getValue() instanceof Field)
									{
										//Get the entry field
										Field entryField = (Field) entry.getValue();
										
										//Get the value
										value = entryField.get(item);															
									}
									
									//Add constant
									else value = entry.getValue();
									
									//Update object
									Field updateField = childColumnsFliped.get(entry.getKey());
									if(updateField != null)
										updateField.set(mapItem, value);
								}
																											
								//Set the parameters
								setParamters(statement, childSquirrel.getFields(), childSquirrel.getAutoIncrementField(), mapItem);
													
								//Run the statement
								statement.execute();	
								
								//Set the auto ID if auto increment is set
								if(squirrel.getAutoIncrementField() != null)
								{
									//Get the result
									result = statement.getGeneratedKeys();		
									
									//Move to the value
									result.next();
									
									//Read the result into object auto field
									writeValue(result, 1, childSquirrel.getAutoIncrementField(), mapItem);	
									
									//Close result
									result.close();
								}	
								
								//Insert links for the item
								insertLinks(connection, mapItem, depth-1);
							}
						}
					}					
					
					//Insert single item
					else
					{						
						//Get the child squirrel
						SquirrelObject childSquirrel = this.getSquirrelObject(childItem);
						
						//Get the child columns 
						Map<Field, String> childColumns = childSquirrel.getColumns();						
						
						//Flip the child columns
						Map<String, Field> childColumnsFliped = new HashMap<String, Field>();
						for(Entry<Field, String> entry : childColumns.entrySet())
							childColumnsFliped.put(entry.getValue(), entry.getKey());					
										
						//Set the fields to the item
						for(Entry<String, Object> entry : columns.entrySet())
						{	
							//Create the value
							Object value = null;
							
							//Check if the value is a field
							if(entry.getValue() instanceof Field)
							{
								//Get the entry field
								Field entryField = (Field) entry.getValue();
								
								//Get the value
								value = entryField.get(item);															
							}
							
							//Add constant
							else value = entry.getValue();
							
							//Update the child object
							Field updateField = childColumnsFliped.get(entry.getKey());
							if(updateField != null)
								updateField.set(childItem, value);
						}
						
						//Get the SQL
						String sql = childSquirrel.getInsert();
									
						//Create the statement 
						statement = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
						
						//Set the parameters
						setParamters(statement, childSquirrel.getFields(), childSquirrel.getAutoIncrementField(), childItem);
											
						//Run the statement
						statement.execute();	
						
						//Set the auto ID if auto increment is set
						if(squirrel.getAutoIncrementField() != null)
						{
							//Get the result
							result = statement.getGeneratedKeys();		
							
							//Move to the value
							result.next();
							
							//Read the result into object auto field
							writeValue(result, 1, childSquirrel.getAutoIncrementField(), childItem);						
						}	
						
						//Insert links for the item
						insertLinks(connection, childItem, depth-1);						
					}
				}				
			}						
		}

		//Failed
		catch(Exception ex)
		{
			throw new SquirrelException("Failed to insert links for " + item.getClass().getSimpleName(), ex);
		}
		
		//Clean up
		finally
		{
			try{if(result != null) result.close();} catch(Throwable t){}
			try{statement.close();} catch(Throwable t){}
		}
	}
	
	/**
	 * Delete the object links for the item.
	 * <p>
	 * This only deletes the object links that are currently
	 * set on the item, i.e. that are in the object graph. 
	 * Normally you probably want to delete all objects which
	 * are "linked" to the item in the database.
	 * </p>
	 * @param <T>
	 * @param item
	 * @throws SquirrelException
	 */
	public <T> void deleteLinks(T item) throws SquirrelException
	{
		//Fields
		Connection connection = null;
		
		//Try to delete links
		try
		{
			//Get a connection
			connection = getConnection();
						
			//Delete the links for the item
			deleteLinks(connection, item);			
		}
		
		//Failed
		catch(Exception ex)
		{			
			//Just re-throw if SquirrelException
			if(ex instanceof SquirrelException)
				throw (SquirrelException) ex;
			
			//Throw wrapped exception
			else throw new SquirrelException("Failed to delete links for " + item.getClass().getSimpleName(), ex);
		}
		
		//Clean up
		finally
		{
			try{connection.close();} catch(Throwable t){}
		}
	}		
	
	/**
	 * Delete the object links for the item.
	 * <p>
	 * This only deletes the object links that are currently
	 * set on the item, i.e. that are in the object graph. 
	 * Normally you probably want to delete all objects which
	 * are "linked" to the item in the database.
	 * </p>
	 * @param <T>
	 * @param connection
	 * @param item 
	 * @throws SquirrelException
	 */	
	public <T> void deleteLinks(Connection connection, T item) throws SquirrelException
	{
		deleteLinks(connection, item);
	}
	
	/**
	 * Delete the object links for the item.
	 * <p>
	 * This only deletes the object links that are currently
	 * set on the item, i.e. that are in the object graph. 
	 * Normally you probably want to delete all objects which
	 * are "linked" to the item in the database.
	 * </p>
	 * @param <T>
	 * @param connection
	 * @param item
	 * @param depth 
	 * @throws SquirrelException
	 */
	private <T> void deleteLinks(Connection connection, T item, Integer depth) throws SquirrelException
	{
		//Fields
		PreparedStatement statement = null;
		ResultSet result = null;		
		
		//Try to delete the links
		try
		{						
			//Get the SquirrelObject
			SquirrelObject squirrel = this.getSquirrelObject(item); 
			
			//Get link fields
			Set<Field> linkSet = squirrel.getLinkInsertDeleteFields();
						
			//Get the link map
			Map<Field, Map<String, Object>> linkMap = squirrel.getLinkMap();
						
			//Load the link fields
			for(Field field : linkSet)
			{
				//Get the depth if not set
				if(depth == null)
					depth = squirrel.getLinkDepth().get(field);
				
				//Go to the next field if less then one
				if(depth < 1)
					continue;
				
				//Get the child item
				Object childItem = field.get(item);
								
				//Check that child item has been set
				if(childItem != null)
				{					
					//Get the class
					Class<?> clazz = null;
					
					//List
					if(childItem instanceof List<?>)
					{
						//Get the type
						ParameterizedType type = (ParameterizedType) field.getGenericType();
						
						//Get the class
						clazz = (Class<?>) type.getActualTypeArguments()[0];
						
						//Get the list
						List<?> list = (List<?>) field.get(item);
						
						//Delete links for child items
						for(Object listItem : list)
							deleteLinks(connection, listItem, depth-1);				
					}
					
					//Map
					else if(childItem instanceof Map<?, ?>)
					{
						//Get the type
						ParameterizedType type = (ParameterizedType) field.getGenericType();
						
						//Get the class
						clazz = (Class<?>) type.getActualTypeArguments()[1];
						
						//Get the map
						Map<?, ?> map = (Map<?, ?>) field.get(item);
						
						//Delete links for child items
						for(Object mapItem : map.values())
							deleteLinks(connection, mapItem, depth-1);				
					}					
					
					//Single item
					else 
					{
						//Get the class
						clazz = field.getType();
						
						//Delete links for the sub item
						deleteLinks(connection, childItem, depth-1);
					}
					
					//Create the string builder
					StringBuilder builder = new StringBuilder();
					
					//Get the table name
					String tableName = SquirrelWriter.getTableName(this, clazz);
					
					//Add the delete to the builder
					builder.append("DELETE FROM ");
					builder.append(tableName);
					builder.append(" WHERE ");
					
					//Get the columns
					Map<String, Object> columns = linkMap.get(field);
													
					//Create the parameters map
					Map<String, Object> parameters = new LinkedHashMap<String, Object>();
					
					//Populate the parameters map
					for(Entry<String, Object> entry : columns.entrySet())
					{	
						//Add the parameter to the SQL
						builder.append(entry.getKey());
						builder.append(" = ? AND ");
						
						//Check if the value is a field
						if(entry.getValue() instanceof Field)
						{
							//Get the entry field
							Field entryField = (Field) entry.getValue();
							
							//Get the value
							Object value = entryField.get(item);
							
							//Add to the parameters
							parameters.put(entry.getKey(), value);
						}
						
						//Primitive value just add to the parameters
						else parameters.put(entry.getKey(), entry.getValue());
					}
					
					//Remove the last and
					builder.delete(builder.length()-4, builder.length());				
					
					//Create the statement
					statement = connection.prepareStatement(builder.toString());;
					
					//Set the parameters
					setParamtersMap(statement, parameters);
					
					//Run the statement
					statement.execute();				
				}		
			}
		}

		//Failed
		catch(Exception ex)
		{
			throw new SquirrelException("Failed to delete links for " + item.getClass().getSimpleName(), ex);
		}
		
		//Clean up
		finally
		{
			try{if(result != null) result.close();} catch(Throwable t){}
			try{statement.close();} catch(Throwable t){}
		}		
	}
	
	/**
	 * Get squirrel object.
	 * <p>
	 * This creates the squirrel object if it has not
	 * already been created.
	 * </p>
	 * @param <T>
	 * @param item
	 * @return
	 * @throws SquirrelException
	 */
	private <T> SquirrelObject getSquirrelObject(T item) throws SquirrelException
	{
		return getSquirrelObject(item.getClass());
	}
	
	/**
	 * Get squirrel object.
	 * <p>
	 * This creates the squirrel object if it has not
	 * already been created.
	 * </p>
	 * @param clazz
	 * @throws SquirrelException
	 */	
	private SquirrelObject getSquirrelObject(Class<?> clazz) throws SquirrelException
	{
		//Get the squirrel object
		SquirrelObject squirrel = objects.get(clazz);
		
		//Create and return the squirrel
		if(squirrel == null)
		{
			//Create the squirrel object
			createSquirrelObject(clazz);
			
			//Return the squirrel object
			return objects.get(clazz);
		}
		
		//Return the squirrel
		else return squirrel;
	}
	
	/**
	 * Create the Squirrel object for a class
	 * @param clazz
	 * @throws SquirrelException
	 */
	private void createSquirrelObject(Class<?> clazz) throws SquirrelException
	{										
		//Create the Squirrel object
		SquirrelObject squirrel = new SquirrelObject(this, clazz);
						
		//Add to the objects to the map if not already added
		objects.putIfAbsent(clazz, squirrel);
	}	
	
	/**
	 * Set the parameters to a statement.
	 * @param <T>
	 * @param statement
	 * @param fields
	 * @param item
	 * @throws SquirrelException
	 */
	protected static <T> void setParamters(PreparedStatement statement, Set<Field> fields, T item) throws SquirrelException
	{
		setParamters(statement, 1, fields, null, item);
	}
	
	/**
	 * Set the parameters to a statement.
	 * <p>
	 * The index field is the index for the statement 
	 * parameters. 
	 * </p>
	 * @param <T>
	 * @param statement
	 * @param index
	 * @param fields
	 * @param item
	 * @throws SquirrelException
	 */
	protected static <T> void setParamters(PreparedStatement statement, int index, Set<Field> fields, T item) throws SquirrelException
	{
		setParamters(statement, index, fields, null, item);
	}		

	/**
	 * Set the parameters to a statement.
	 * <p>
	 * The skip field will not be added to the statement, this 
	 * is used for auto increment columns.
	 * </p>
	 * @param <T>
	 * @param statement
	 * @param fields
	 * @param skip
	 * @param item
	 * @throws SquirrelException
	 */
	protected static <T> void setParamters(PreparedStatement statement, Set<Field> fields, Field skip, T item) throws SquirrelException
	{
		setParamters(statement, 1, fields, skip, item);
	}

	/**
	 * Set the parameters to a statement.
	 * <p>
	 * The skip field will not be added to the statement, this 
	 * is used for auto increment columns.
	 * </p>
	 * <p>
	 * The index field is the index for the statement 
	 * parameters. 
	 * </p>
	 * @param <T>
	 * @param statement
	 * @param index The index to start from.
	 * @param fields
	 * @param skip
	 * @param item
	 * @throws SquirrelException
	 */
	protected static <T> void setParamters(PreparedStatement statement, int index, Set<Field> fields, Field skip, T item) throws SquirrelException
	{
		//Try to set the parameters
		try
		{					
			//Set the parameter(s)
			for(Field field : fields)
			{		
				//Set if not to skip
				if(field != skip)
				{					
					//Get the class
					Class<?> type = field.getType();
		
					//Add as string
					if(type == String.class)
						statement.setString(index++, (String) field.get(item));
					
					//Add as char
					else if(type == Character.TYPE)
						statement.setString(index++, String.valueOf(field.getChar(item)));
					
					//Add as integer
					else if(type == Integer.TYPE)
						statement.setInt(index++, field.getInt(item));
					
					//Add as long
					else if(type == Long.TYPE)
						statement.setLong(index++, field.getLong(item));							
					
					//Add as double
					else if(type == Double.TYPE)
						statement.setDouble(index++, field.getDouble(item));
					
					//Add as float
					else if(type == Float.TYPE)
						statement.setFloat(index++, field.getFloat(item));					
					
					//Add as date 
					else if(type == Date.class)	
					{	
						//Get the date
						Date date = (Date) field.get(item);
						
						//NULL 
						if(date == null)
							statement.setDate(index++, null);
						
						//Set
						else statement.setTimestamp(index++, new Timestamp(date.getTime()));
					}	
								
					//Add as boolean
					else if(type == Boolean.TYPE)
						statement.setBoolean(index++, field.getBoolean(item));
					
					//Add as Enum
					else if(type.isEnum())
					{	
						//Get the enum
						Enum<?> e = (Enum<?>) field.get(item);	
						
						//Null
						if(e == null)
							statement.setString(index++, null);
					
						//Set
						else statement.setString(index++, e.name());							
					}	
					
					//Add as float
					else if(type == Float.TYPE)
						statement.setFloat(index++, field.getFloat(item));				
					
					//Add as short
					else if(type == Short.TYPE)
						statement.setShort(index++, field.getShort(item));						
					
					//Unmapped type
					else throw new SquirrelException("Un-mapped type found on item: " + field.getName() + ":" + type.getSimpleName());
				}
			}			
		}
		
		//Failed
		catch(Exception ex)
		{
			throw new SquirrelException("Failed to set parameters", ex);
		}		
	}
	
	/**
	 * Set the parameters with overrides from the field.
	 * @param <T>
	 * @param statement
	 * @param fields
	 * @param skip
	 * @param columns
	 * @param overrides
	 * @param item
	 * @throws SquirrelException
	 */
	protected static <T> void setParamtersOverride(PreparedStatement statement, Set<Field> fields, Field skip, Map<Field, String> columns,  Map<String, Object> overrides, T item) throws SquirrelException
	{
		//Try to set the parameters
		try
		{					
			//Create the index
			int index = 1; 
						
			//Set the parameter(s)
			for(Field field : fields)
			{		
				//Set if not to skip
				if(field != skip)
				{					
					//Get the class
					Class<?> type = field.getType();
					
					//Get the value
					Object value = overrides.get(columns.get(field));
										
					//Set the value
					if(value == null)
						value = field.get(item);
							
					//Add as string
					if(value.getClass() == String.class)
						statement.setString(index++, (String) field.get(item));
					
					//Add as char
					else if(value.getClass() == Character.TYPE)
						statement.setString(index++, String.valueOf(field.getChar(item)));
					
					//Add as integer
					else if(value.getClass() == Integer.TYPE)
						statement.setInt(index++, field.getInt(item));
					
					//Add as long
					else if(value.getClass() == Long.TYPE)
						statement.setLong(index++, field.getLong(item));						
					
					//Add as double
					else if(value.getClass() == Double.TYPE)
						statement.setDouble(index++, field.getDouble(item));
					
					//Add as float
					else if(value.getClass() == Float.TYPE)
						statement.setFloat(index++, field.getFloat(item));					
					
					//Add as date 
					else if(value.getClass() == Date.class)	
					{	
						//Get the date
						Date date = (Date) field.get(item);
						
						//NULL 
						if(date == null)
							statement.setDate(index++, null);
						
						//Set
						else statement.setTimestamp(index++, new Timestamp(date.getTime()));
					}	
								
					//Add as boolean
					else if(value.getClass() == Boolean.TYPE)
						statement.setBoolean(index++, field.getBoolean(item));
					
					//Add as Enum
					else if(value.getClass().isEnum())
					{	
						//Get the enum
						Enum<?> e = (Enum<?>) field.get(item);	
						
						//Null
						if(e == null)
							statement.setString(index++, null);
					
						//Set
						else statement.setString(index++, e.name());							
					}	
					
					//Add as float
					else if(value.getClass() == Float.TYPE)
						statement.setFloat(index++, field.getFloat(item));				
					
					//Add as short
					else if(value.getClass() == Short.TYPE)
						statement.setShort(index++, field.getShort(item));						
					
					//Unmapped type
					else throw new SquirrelException("Un-mapped type found on item: " + field.getName() + ":" + type.getSimpleName());
				}
			}			
		}
		
		//Failed
		catch(Exception ex)
		{
			throw new SquirrelException("Failed to set parameters", ex);
		}		
	}	

	/**
	 * 
	 * @param <T>
	 * @param statement
	 * @param parameters
	 * @throws SquirrelException
	 */
	protected static <T> void setParamtersMap(PreparedStatement statement, Map<String, Object> parameters) throws SquirrelException
	{
		//Try to set the parameters
		try
		{		
			//Create the index
			int index = 1;
			
			//Set the parameter(s)
			for(Entry<String, Object> entry : parameters.entrySet())
			{	
				//Get the field
				String field = entry.getKey();
				
				//Get the value
				Object value = entry.getValue();
					
				//Get the type of the value
				Class<?> type = value.getClass();	
								
				//Add as string
				if(type == String.class)
					statement.setString(index++, (String) value);
				
				//Add as char
				else if(type == Character.class)
					statement.setString(index++, String.valueOf(value));
				
				//Add as integer
				else if(type == Integer.class)
					statement.setInt(index++, (Integer) value);
				
				//Add as long
				else if(type == Long.class)
					statement.setLong(index++, (Long) value);				
				
				//Add as double
				else if(type == Double.class)
					statement.setDouble(index++, (Double) value);
				
				//Add as date 
				else if(type == Date.class)	
				{	
					//Get the date
					Date date = (Date) value;
					
					//NULL 
					if(date == null)
						statement.setDate(index++, null);
					
					//Set
					else statement.setTimestamp(index++, new Timestamp(date.getTime()));
				}	
				
				//Add as Enum
				else if(type.isEnum())
				{	
					//Get the enum
					Enum<?> en = (Enum<?>) value;
					
					//Set the enum as string
					statement.setString(index++, en.name());					
				}				
							
				//Add as boolean
				else if(type == Boolean.class)
					statement.setBoolean(index++, (Boolean) value);
				
				//Add as float
				else if(type == Float.class)
					statement.setFloat(index++, (Float) value);				
				
				//Add as short
				else if(type == Short.class)
					statement.setShort(index++, (Short) value);						
				
				//Unmapped type
				else throw new SquirrelException("Un-mapped type found on parameters map: " + field + ":" + type.getSimpleName());				
			}			
		}
		
		//Failed
		catch(Exception ex)
		{
			throw new SquirrelException("Failed to set parameters map", ex);
		}		
	}	
	
	/**
	 * Write a value into a item field.
	 * @param <T>
	 * @param result
	 * @param field
	 * @param item
	 * @throws SquirrelException
	 */
	protected static <T> void writeValue(ResultSet result, int index, Field field, T item) throws SquirrelException
	{
		//Try to read the value into the object field
		try
		{
			//Get the class
			Class<?> type = field.getType();

			//Get string
			if(type == String.class)
				field.set(item, result.getString(index));
			
			//Add as char
			else if(type == Character.TYPE)
				field.setChar(item, result.getString(index).charAt(0));
			
			//Add as integer
			else if(type == Integer.TYPE)
				field.setInt(item, result.getInt(index));
			
			//Add as long
			else if(type == Long.TYPE)
				field.setLong(item, result.getLong(index));			
			
			//Add as double
			else if(type == Double.TYPE)
				field.setDouble(item, result.getDouble(index));
			
			//Add as float
			else if(type == Float.TYPE)
				field.setFloat(item, result.getFloat(index));			
			
			//Add as date 
			else if(type == Date.class)					
			{	
				//Get the date
				Timestamp value = result.getTimestamp(index);
				
				//NULL
				if(value == null)
					field.set(item, null);

				//Set
				else
				{
					//Create the date
					Date date = new Date(value.getTime());

					//Set the date to the item
					field.set(item, date);
				}
			}	
						
			//Add as boolean
			else if(type == Boolean.TYPE)
				field.setBoolean(item, result.getBoolean(index));
			
			//Add as Enum
			else if(type.isEnum())
			{
				//Get the value as string
				String value = result.getString(index);
				
				//Get the enum types
				Enum<?>[] enums = (Enum[]) type.getEnumConstants();
				
				//Try to find a match
				for(Enum<?> e : enums)
				{
					//Match
					if(e.name().equals(value))
					{
						//Set the enum to the item
						field.set(item, e);
					
						//Stop
						return;
					}
				}
				
				//Set to null
				field.set(item, null);				
			}
			
			//Add as float
			else if(type == Float.TYPE)
				field.setFloat(item, result.getFloat(index));				
			
			//Add as short
			else if(type == Short.TYPE)
				field.setShort(item, result.getShort(index));							
			
			//Unmapped type
			else throw new SquirrelException("Un-mapped type found on item: " + field.getName() + ":" + type.getSimpleName());			
		}
		
		//Failed
		catch(Exception ex)
		{
			throw new SquirrelException("Failed to read value", ex);
		}		
	}
	
	/**
	 * Write a value into a item field.
	 * @param <T>
	 * @param result
	 * @param column
	 * @param field
	 * @param item
	 * @throws SquirrelException
	 */
	protected static <T> void writeValue(ResultSet result, String column, Field field, T item) throws SquirrelException
	{
		//Try to read the value into the object field
		try
		{
			//Get the class
			Class<?> type = field.getType();
			
			//Get string
			if(type == String.class)
				field.set(item, result.getString(column));
			
			//Add as char
			else if(type == Character.TYPE)
				field.setChar(item, result.getString(column).charAt(0));
			
			//Add as integer
			else if(type == Integer.TYPE)
				field.setInt(item, result.getInt(column));
			
			//Add as long
			else if(type == Long.TYPE)
				field.setLong(item, result.getLong(column));			
			
			//Add as double
			else if(type == Double.TYPE)
				field.setDouble(item, result.getDouble(column));
			
			//Add as float
			else if(type == Float.TYPE)
				field.setFloat(item, result.getFloat(column));			
			
			//Add as date 
			else if(type == Date.class)					
			{	
				//Get the date
				Timestamp value = result.getTimestamp(column);
				
				//NULL
				if(value == null)
					field.set(item, null);

				//Set
				else
				{
					//Create the date
					Date date = new Date(value.getTime());

					//Set the date to the item
					field.set(item, date);
				}
			}	
						
			//Add as boolean
			else if(type == Boolean.TYPE)
				field.setBoolean(item, result.getBoolean(column));
			
			//Add as Enum
			else if(type.isEnum())
			{
				//Get the value as string
				String value = result.getString(column);
				
				//Get the enum types
				Enum<?>[] enums = (Enum[]) type.getEnumConstants();
				
				//Try to find a match
				for(Enum<?> e : enums)
				{
					//Match
					if(e.name().equals(value))
					{
						//Set the enum to the item
						field.set(item, e);
					
						//Stop
						return;
					}
				}
				
				//Set to null
				field.set(item, null);				
			}
			
			//Add as float
			else if(type == Float.TYPE)
				field.setFloat(item, result.getFloat(column));				
			
			//Add as short
			else if(type == Short.TYPE)
				field.setShort(item, result.getShort(column));							
			
			//Unmapped type
			else throw new SquirrelException("Un-mapped type found on item: " + field.getName() + ":" + type.getSimpleName());			
		}
		
		//Failed
		catch(Exception ex)
		{
			throw new SquirrelException("Failed to read value", ex);
		}		
	}	
	
	/**
	 * Execute a raw SQL string.
	 * @param sql
	 * @throws SquirrelException
	 */
	public void execute(String sql) throws SquirrelException
	{
		//Fields
		Connection connection = null;
		
		//Try to update the item
		try
		{
			//Get a connection
			connection = getConnection();
			
			//Set auto commit
			connection.setAutoCommit(false);
			
			//Execute
			execute(connection, sql);
						
			//Commit
			connection.commit();			
		}
		
		//Failed
		catch(Exception ex)
		{
			//Rollback
			try{connection.rollback();} catch(Throwable t){}
			
			//Just re-throw if SquirrelException
			if(ex instanceof SquirrelException)
				throw (SquirrelException) ex;
			
			//Throw wrapped exception
			else
				throw new SquirrelException("Failed to execute SQL string", ex);
		}
		
		//Clean up
		finally
		{
			try{connection.close();} catch(Throwable t){}
		}
	}
	
	/**
	 * Execute a raw SQL string.
	 * @param connection
	 * @param sql
	 * @throws SquirrelException
	 */
	public void execute(Connection connection, String sql) throws SquirrelException
	{		
		//Try to execute
		try
		{
			connection.createStatement().execute(sql);
		}
		
		//Failed
		catch(Exception ex)
		{
			throw new SquirrelException("Failed to execute SQL string", ex);
		}		
	}
	
	/**
	 * Get a connection to the database.
	 * @return
	 * @throws SquirrelException
	 */
	public Connection getConnection() throws SquirrelException
	{
		//Try to get a connection
		try
		{
			return getSource().getConnection();
		}
		
		//Failed
		catch(Exception ex)
		{
			throw new SquirrelException("Failed to get a connection", ex);
		}
	}

	/** 
	 * Get the source
	 * @return the source
	 */
	public DataSource getSource()
	{
		return source;
	}
	
}
